package com.meta.nd.service.filter.utils;

import com.meta.nd.filter.bo.FilterParamDef;
import com.meta.nd.filter.vo.FilterFd;
import com.meta.nd.service.filter.bo.OrderByInfo;
import com.meta.nd.service.filter.bo.SQLReturnInfo;
import com.meta.nd.service.filter.bo.TableJoin;
import com.meta.nd.service.filter.bo.TableTree;
import com.meta.nd.vo.Nd;
import org.springframework.util.StringUtils;

import java.util.Comparator;
import java.util.Map;

public class SqlBuilder {

public static String build(Nd nd, FilterParamDef filterDef){
    TableTree tableTree = TableTreeBuilder.build(nd, filterDef);
    String sql = SqlBuilder.buildByTree(tableTree);
    return sql;
}

    public static String buildTotalSql(Nd nd, FilterParamDef filterDef){
        TableTree tableTree = TableTreeBuilder.build(nd, filterDef);
        String sql = SqlBuilder.buildTotalSql(tableTree);
        return sql;
    }



private static String buildByTree(TableTree tableTree){
    SQLReturnInfo sqlReturnInfo = buildUnion(tableTree);
    String embedWhere = buildEmbedCondition(tableTree);
    if (!StringUtils.isEmpty(embedWhere))
        sqlReturnInfo.getConditionWheres().add(embedWhere);
    return buildConditionSql(sqlReturnInfo);
}

    private static String buildTotalSql(TableTree tableTree){
        SQLReturnInfo sqlReturnInfo = buildUnion(tableTree);
        String embedWhere = buildEmbedCondition(tableTree);
        if (!StringUtils.isEmpty(embedWhere))
            sqlReturnInfo.getConditionWheres().add(embedWhere);
        return buildGetCountSql(sqlReturnInfo);
    }


    public static void extractConditionFdValues(TableTree tableTree, Map<String,Object> map){
        for (FilterFd conditionFd :tableTree.getFilterFds()){
            if ( conditionFd.getValue() != null && !StringUtils.isEmpty(conditionFd.getValue().toString())){
                map.put(conditionFd.getFdName(),conditionFd.getValue());
            }
        }
        for( TableTree child : tableTree.getChildren())
            extractConditionFdValues(child,map);
    }

public static SQLReturnInfo buildUnion(TableTree tableTree){
    SQLReturnInfo sqlReturnInfo = new SQLReturnInfo();
    buildUnion(null,tableTree,sqlReturnInfo);
    return sqlReturnInfo;
}

public static void buildUnion(String parentJoinWhere,TableTree tableTree, SQLReturnInfo sqlReturnInfo){
    if (tableTree.isReturn() ){
        TableJoin tableJoin = new TableJoin();
        tableJoin.setJoinWithParent(parentJoinWhere);
        sqlReturnInfo.getResultFdNames().addAll(tableTree.getResultFdNames());
        sqlReturnInfo.getOrderByInfos().addAll(tableTree.getOrderByInfos());
        tableJoin.setTableName(tableTree.getNvTableName());
        sqlReturnInfo.getTableJoins().add(tableJoin);
        for( String fdName : tableTree.getFvJoinFdNames()){
            TableJoin fvTableJoin = new TableJoin();
            String joinWhere = tableTree.getFvJoinsWhere(fdName);
            fvTableJoin.setJoinWithParent(joinWhere);
            fvTableJoin.setTableName(tableTree.getFvTableNameForFdName(fdName));
            sqlReturnInfo.getTableJoins().add(fvTableJoin);
        }
    }
    if ( tableTree.isCondition() && tableTree.isReturn()){
        String where = tableTree.getCurrentWhere();
        sqlReturnInfo.getConditionWheres().add(where);
    }
    if ( tableTree.hasDescendantReturnFd()){
        for( TableTree childTree : tableTree.getChildren()){
            String childJoin  = null;
            if ( childTree.isReturn() || childTree.hasDescendantReturnFd()){
                childJoin = tableTree.getUnionChildWhere(childTree);
            }
            buildUnion(childJoin,childTree,sqlReturnInfo);
        }
    }
}

public static String buildEmbedCondition(TableTree tableTree){
    StringBuffer sb = new StringBuffer();
    for(TableTree childTree: tableTree.getChildren()){
        if (childTree.isCondition() && !childTree.isReturn()
                && !childTree.hasDescendantReturnFd()){
            TableTree newChild = childTree.clone();
            if( newChild.isReverse())
                newChild.setIdAsOnlyReturnFd();
            else
                newChild.setParentIdAsOnlyReturnFd();
            String sql = buildByTree(newChild);
            if ( sb.length() > 0 )
                sb.append(" AND ");
            if ( newChild.isReverse())
                sql = " %s.parentId in (" + sql +")";
            else
                sql = " %s.id in (" + sql +")";
            sql = String.format(sql, tableTree.getNvTableName());
            sb.append(sql);
        }
    }
    if ( sb.length() == 0 )
        return null;
    else
        return sb.toString();
}

public static String buildConditionSql(SQLReturnInfo sqlReturnInfo){
    StringBuffer tableListSb = new StringBuffer();
    StringBuffer fieldListSb = new StringBuffer();
    StringBuffer whereListSb = new StringBuffer();
    for( TableJoin tableJoin : sqlReturnInfo.getTableJoins()){
        if( tableJoin.getJoinWithParent() == null)
            tableListSb.append(tableJoin.getTableName());
        else{
            tableListSb.append(" left join ").append(tableJoin.getTableName());
            tableListSb.append(" on(" + tableJoin.getJoinWithParent() + ")");
        }
    }
    for ( String resultFdName : sqlReturnInfo.getResultFdNames()){
        if ( fieldListSb.length() > 0 )
            fieldListSb.append(",");
        fieldListSb.append( resultFdName );
    }
    for( String where : sqlReturnInfo.getConditionWheres()){
        if ( whereListSb.length() > 0 )
            whereListSb.append(" and ");

        whereListSb.append(where);
    }
    String sql = "SELECT " + fieldListSb.toString() + " FROM " + tableListSb.toString();
    if (whereListSb.length()>0)
        sql += " WHERE " + whereListSb.toString();
    if ( sqlReturnInfo.getOrderByInfos().size() > 0 ){
        sqlReturnInfo.getOrderByInfos().sort(new Comparator<OrderByInfo>() {
            @Override
            public int compare(OrderByInfo o1, OrderByInfo o2) {
                return Math.abs(o1.getSortOrder()) - Math.abs(o2.getSortOrder());
            }
        });
        String orderBySql ="";
        for( OrderByInfo orderByInfo : sqlReturnInfo.getOrderByInfos()){
            String fdOrderBy = orderByInfo.getFdName() + " "  + ( orderByInfo.getSortOrder() > 0 ? "asc" : "desc");
            if ( !StringUtils.isEmpty(orderBySql))
                orderBySql = ", " + fdOrderBy;
            else
                orderBySql = " "  + fdOrderBy;
        }
        sql += " order by" + orderBySql;
    }
    return sql;
}


    public static String buildGetCountSql(SQLReturnInfo sqlReturnInfo){
        StringBuffer tableListSb = new StringBuffer();
        StringBuffer fieldListSb = new StringBuffer();
        StringBuffer whereListSb = new StringBuffer();
        for( TableJoin tableJoin : sqlReturnInfo.getTableJoins()){
            if( tableJoin.getJoinWithParent() == null)
                tableListSb.append(tableJoin.getTableName());
            else {
                tableListSb.append(" left join ").append(tableJoin.getTableName());
                tableListSb.append(" on(" + tableJoin.getJoinWithParent() + ")");
            }
        }
        fieldListSb.append("count(*) as count");
        for( String where : sqlReturnInfo.getConditionWheres()){
            if ( whereListSb.length() > 0 )
                whereListSb.append(" and ");

            whereListSb.append(where);
        }
        String sql = "SELECT " + fieldListSb.toString() + " FROM " + tableListSb.toString();
        if (whereListSb.length()>0)
            sql += " WHERE " + whereListSb.toString();

        return sql;
    }

}
